{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "init_cell": true
   },
   "outputs": [],
   "source": [
    "%logstop\n",
    "%logstart -ortq ~/.logs/pw.py append\n",
    "%matplotlib inline\n",
    "import matplotlib\n",
    "import seaborn as sns\n",
    "sns.set()\n",
    "matplotlib.rcParams['figure.dpi'] = 144"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from static_grader import grader"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PW Miniproject\n",
    "## Introduction\n",
    "\n",
    "The objective of this miniproject is to exercise your ability to use basic Python data structures, define functions, and control program flow. We will be using these concepts to perform some fundamental data wrangling tasks such as joining data sets together, splitting data into groups, and aggregating data into summary statistics.\n",
    "**Please do not use `pandas` or `numpy` to answer these questions.**\n",
    "\n",
    "We will be working with medical data from the British NHS on prescription drugs. Since this is real data, it contains many ambiguities that we will need to confront in our analysis. This is commonplace in data science, and is one of the lessons you will learn in this miniproject."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Downloading the data\n",
    "\n",
    "We first need to download the data we'll be using from Amazon S3:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "mkdir: cannot create directory ‘pw-data’: File exists\n",
      "File ‘./pw-data/201701scripts_sample.json.gz’ already there; not retrieving.\n",
      "\n",
      "File ‘./pw-data/practices.json.gz’ already there; not retrieving.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "mkdir pw-data\n",
    "wget http://dataincubator-wqu.s3.amazonaws.com/pwdata/201701scripts_sample.json.gz -nc -P ./pw-data\n",
    "wget http://dataincubator-wqu.s3.amazonaws.com/pwdata/practices.json.gz -nc -P ./pw-data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the data\n",
    "\n",
    "The first step of the project is to read in the data. We will discuss reading and writing various kinds of files later in the course, but the code below should get you started."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gzip\n",
    "import simplejson as json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "with gzip.open('./pw-data/201701scripts_sample.json.gz', 'rb') as f:\n",
    "    scripts = json.load(f)\n",
    "\n",
    "with gzip.open('./pw-data/practices.json.gz', 'rb') as f:\n",
    "    practices = json.load(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This data set comes from Britain's National Health Service. The `scripts` variable is a list of prescriptions issued by NHS doctors. Each prescription is represented by a dictionary with various data fields: `'practice'`, `'bnf_code'`, `'bnf_name'`, `'quantity'`, `'items'`, `'nic'`, and `'act_cost'`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'bnf_code': '0101010G0AAABAB',\n",
       "  'items': 2,\n",
       "  'practice': 'N81013',\n",
       "  'bnf_name': 'Co-Magaldrox_Susp 195mg/220mg/5ml S/F',\n",
       "  'nic': 5.98,\n",
       "  'act_cost': 5.56,\n",
       "  'quantity': 1000}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scripts[:1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A [glossary of terms](http://webarchive.nationalarchives.gov.uk/20180328130852tf_/http://content.digital.nhs.uk/media/10686/Download-glossary-of-terms-for-GP-prescribing---presentation-level/pdf/PLP_Presentation_Level_Glossary_April_2015.pdf/) and [FAQ](http://webarchive.nationalarchives.gov.uk/20180328130852tf_/http://content.digital.nhs.uk/media/10048/FAQs-Practice-Level-Prescribingpdf/pdf/PLP_FAQs_April_2015.pdf/) is available from the NHS regarding the data. Below we supply a data dictionary briefly describing what these fields mean.\n",
    "\n",
    "| Data field |Description|\n",
    "|:----------:|-----------|\n",
    "|`'practice'`|Code designating the medical practice issuing the prescription|\n",
    "|`'bnf_code'`|British National Formulary drug code|\n",
    "|`'bnf_name'`|British National Formulary drug name|\n",
    "|`'quantity'`|Number of capsules/quantity of liquid/grams of powder prescribed|\n",
    "| `'items'`  |Number of refills (e.g. if `'quantity'` is 30 capsules, 3 `'items'` means 3 bottles of 30 capsules)|\n",
    "|  `'nic'`   |Net ingredient cost|\n",
    "|`'act_cost'`|Total cost including containers, fees, and discounts|"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `practices` variable is a list of member medical practices of the NHS. Each practice is represented by a dictionary containing identifying information for the medical practice. Most of the data fields are self-explanatory. Notice the values in the `'code'` field of `practices` match the values in the `'practice'` field of `scripts`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# scripts[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the following questions we will ask you to explore this data set. You may need to combine pieces of the data set together in order to answer some questions. Not every element of the data set will be used in answering the questions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 1: summary_statistics\n",
    "\n",
    "Our beneficiary data (`scripts`) contains quantitative data on the number of items dispensed (`'items'`), the total quantity of item dispensed (`'quantity'`), the net cost of the ingredients (`'nic'`), and the actual cost to the patient (`'act_cost'`). Whenever working with a new data set, it can be useful to calculate summary statistics to develop a feeling for the volume and character of the data. This makes it easier to spot trends and significant features during further stages of analysis.\n",
    "\n",
    "Calculate the sum, mean, standard deviation, and quartile statistics for each of these quantities. Format your results for each quantity as a list: `[sum, mean, standard deviation, 1st quartile, median, 3rd quartile]`. We'll create a `tuple` with these lists for each quantity as a final result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def describe(key):\n",
    "    \n",
    "    #     prepare list\n",
    "    wl = []\n",
    "    for i in scripts:\n",
    "        wl.append(i[key])\n",
    "    wl = sorted(wl)\n",
    "    l = len(wl)\n",
    "\n",
    "    \n",
    "    #total\n",
    "    total=0.0\n",
    "    total = sum(wl)\n",
    "\n",
    "    \n",
    "    #avg\n",
    "    avg = 0.0;\n",
    "    avg = total/l\n",
    "        \n",
    "    \n",
    "    #med\n",
    "    med=0.0\n",
    "    if not l % 2:\n",
    "        # the condition executes if it evaluates as zero\n",
    "        med = (wl[int(l/2)] + wl[int(l/2-1)])/2.0 #for even len\n",
    "    else:\n",
    "        med = wl[int(l/2)] # for odd len\n",
    "        \n",
    "        \n",
    "    #s standard deviation\n",
    "    s = 0.0\n",
    "    v = 0.0\n",
    "    for i in scripts:\n",
    "        v += (i[key] - avg)**2\n",
    "        s = (v/l)**0.5 # **0.5 - squareroot\n",
    "    \n",
    "    \n",
    "    #quartiles\n",
    "    if not l%2:\n",
    "        q25 = wl[int(l*0.25)]\n",
    "        q75 = wl[(int(l*0.75))]\n",
    "    \n",
    "    else:\n",
    "        q25 = wl[int(l*0.25)]\n",
    "        q75 = wl[(int(l*0.75)-1)]\n",
    "    \n",
    "    #print('wl=',wl)\n",
    "    \n",
    "    return (total, avg, s, q25, med, q75)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary =  [('items', describe('items')),\n",
    "           ('quantity', describe('quantity')),\n",
    "           ('nic', describe('nic')),\n",
    "           ('act_cost', describe('act_cost'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# [('items', (4410054.0, 11.522744731217633, 35.05979472066032, 1, 3.0, 8)), ('quantity', (316356836.0, 826.5883059943667, 3959.423447612624, 30, 120.0, 466)), ('nic', (29048309.790000338, 75.89844899484315, 211.64970205862195, 7.7, 22.62, 65.94)), ('act_cost', (27053937.599999707, 70.68748295124895, 196.42716319851132, 7.25, 21.24, 61.53))]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.pw__summary_statistics(summary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 2: most_common_item\n",
    "\n",
    "Often we are not interested only in how the data is distributed in our entire data set, but within particular groups -- for example, how many items of each drug (i.e. `'bnf_name'`) were prescribed? Calculate the total items prescribed for each `'bnf_name'`. What is the most commonly prescribed `'bnf_name'` in our data?\n",
    "\n",
    "To calculate this, we first need to split our data set into groups corresponding with the different values of `'bnf_name'`. Then we can sum the number of items dispensed within in each group. Finally we can find the largest sum.\n",
    "\n",
    "We'll use `'bnf_name'` to construct our groups. You should have *5619* unique values for `'bnf_name'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "bnf_names = [i['bnf_name'] for i in scripts]\n",
    "bnf_names = list(set(bnf_names))\n",
    "assert(len(bnf_names) == 5619)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to construct \"groups\" identified by `'bnf_name'`, where each group is a collection of prescriptions (i.e. dictionaries from `scripts`). We'll construct a dictionary called `groups`, using `bnf_names` as the keys. We'll represent a group with a `list`, since we can easily append new members to the group. To split our `scripts` into groups by `'bnf_name'`, we should iterate over `scripts`, appending prescription dictionaries to each group as we encounter them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#refactored\n",
    "groups = {name: [] for name in bnf_names}\n",
    "for script in scripts:\n",
    "    groups[script['bnf_name']].append(script['items'])\n",
    "groupd = groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_item=['',0]\n",
    "for k,v in groups.items():\n",
    "    groups[k]=sum(v)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "largest = max(groups.values())\n",
    "for k,v in groups.items():\n",
    "    if v == largest:\n",
    "        max_item[1]=v\n",
    "        max_item[0]=k"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we've constructed our groups we should sum up `'items'` in each group and find the `'bnf_name'` with the largest sum. The result, `max_item`, should have the form `[(bnf_name, item total)]`, e.g. `[('Foobar', 2000)]`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_item=[tuple(max_item)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Omeprazole_Cap E/C 20mg', 113826)]\n"
     ]
    }
   ],
   "source": [
    "            #2nd Method\n",
    "# groups = {name: [] for name in bnf_names}\n",
    "# for script in scripts:\n",
    "#     groups[script['bnf_name']].append(script)\n",
    "# largestItem =0\n",
    "# for lis in groups.values():\n",
    "#     total=0\n",
    "#     for dic in lis:\n",
    "#         for item in dic.items():\n",
    "#             item = list(item)\n",
    "#             if item[0]=='items':\n",
    "#                 total+=item[1]\n",
    "#     if total>largestItem:\n",
    "#             largestItem=total\n",
    "#             bnf_name = dic['bnf_name']\n",
    "# max_item = [(bnf_name,largestItem)]\n",
    "# print(max_item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**TIP:** If you are getting an error from the grader below, please make sure your answer conforms to the correct format of `[(bnf_name, item total)]`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.pw__most_common_item(max_item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adding new item to a tuple\n",
    "# x=(3,4)\n",
    "# x= x+(3, )\n",
    "# for i in x:\n",
    "#     print(i)\n",
    "\n",
    "x=('Xismox XL 60_Tab 60mg',)\n",
    "x=x[0].strip()\n",
    "type(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Challenge:** Write a function that constructs groups as we did above. The function should accept a list of dictionaries (e.g. `scripts` or `practices`) and a tuple of fields to `groupby` (e.g. `('bnf_name')` or `('bnf_name', 'post_code')`) and returns a dictionary of groups. The following questions will require you to aggregate data in groups, so this could be a useful function for the rest of the miniproject."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #         method 1\n",
    "# def group_by_field(data, *args):\n",
    "#     field_list = []\n",
    "#     for var in args:\n",
    "#         field_list.append(var)\n",
    "#     if len(field_list)>1:\n",
    "#         keys = [(datum[field_list[0]], datum[field_list[1]]) for datum in data]\n",
    "#     else:\n",
    "#         keys = [(datum[field_list[0]]) for datum in data]\n",
    "#     keys = list(set(keys))\n",
    "#     groups = {key:[] for key in keys}             \n",
    "#     return groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#refactored and robust\n",
    "def group_by_field2(data, *args, to_find=False):\n",
    "    field_list = []\n",
    "    for var in args:\n",
    "        field_list.append(var)\n",
    "    keys = {tuple(datum[lis] for lis in field_list) for datum in data}\n",
    "    if len(field_list)==1:\n",
    "        keys = {(key[0].strip()) for key in keys}\n",
    "    groups = {key:[] for key in keys}\n",
    "    if to_find and len(field_list)==1:\n",
    "        for datum in data:\n",
    "                groups[datum[field_list[0]]].append(datum[str(to_find)])\n",
    "    elif not to_find and len(field_list)==1:\n",
    "        for datum in data:\n",
    "            groups[datum[field_list[0]]].append(datum)\n",
    "    else:\n",
    "        for datum in data:\n",
    "            name = tuple(datum[field] for field in field_list)\n",
    "            groups[name].append(datum)\n",
    "    return groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#test for more than one field\n",
    "groupl=group_by_field2(scripts, 'bnf_name','bnf_code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    " # test for one field\n",
    "groupl2=group_by_field2(scripts, 'bnf_name',to_find='items')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_max_item=['',0]\n",
    "for k,v in groupl2.items():\n",
    "    groupl2[k]=sum(v)\n",
    "largest = max(groupl2.values())\n",
    "\n",
    "for k,v in groupl2.items():\n",
    "    if v == largest:\n",
    "        test_max_item[1]=v\n",
    "        test_max_item[0]=k\n",
    "test_max_item=[tuple(test_max_item)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert test_max_item == max_item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 3: postal_totals\n",
    "\n",
    "Our data set is broken up among different files. This is typical for tabular data to reduce redundancy. Each table typically contains data about a particular type of event, processes, or physical object. Data on prescriptions and medical practices are in separate files in our case. If we want to find the total items prescribed in each postal code, we will have to _join_ our prescription data (`scripts`) to our clinic data (`practices`).\n",
    "\n",
    "Find the total items prescribed in each postal code, representing the results as a list of tuples `(post code, total items prescribed)`. Sort your results ascending alphabetically by post code and take only results from the first 100 post codes. Only include post codes if there is at least one prescription from a practice in that post code.\n",
    "\n",
    "**NOTE:** Some practices have multiple postal codes associated with them. Use the alphabetically first postal code."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can join `scripts` and `practices` based on the fact that `'practice'` in `scripts` matches `'code'` in `practices`. However, we must first deal with the repeated values of `'code'` in `practices`. We want the alphabetically first postal codes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Challenge:** This is an aggregation of the practice data grouped by practice codes. Write an alternative implementation of the above cell using the `group_by_field` function you defined previously."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "practice_postal  = group_by_field2(practices, 'code', to_find='post_code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "for key, value in practice_postal.items():\n",
    "    practice_postal[key]=min(value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert practice_postal['K82019'] == 'HP21 8TR'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can join `practice_postal` to `scripts`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = scripts[:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#adding a new key:value pair\n",
    "for item in joined:\n",
    "    item['postal_code']=practice_postal[item['practice']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally we'll group the prescription dictionaries in `joined` by `'post_code'` and sum up the items prescribed in each group, as we did in the previous question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "items_by_post = group_by_field2(joined, 'postal_code',to_find='items')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k,v in items_by_post.items():\n",
    "    items_by_post[k]=sum(v)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "items_by_post=[(k,v) for k,v in items_by_post.items()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "118"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items_by_post=sorted(items_by_post)\n",
    "len(items_by_post)\n",
    "# dict(items_by_post)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.pw__postal_totals(items_by_post[:100])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Question 4: items_by_region\n",
    "\n",
    "Now we'll combine the techniques we've developed to answer a more complex question. Find the most commonly dispensed item in each postal code, representing the results as a list of tuples (`post_code`, `bnf_name`, amount dispensed as proportion of total). Sort your results ascending alphabetically by post code and take only results from the first 100 post codes.\n",
    "\n",
    "**NOTE:** We'll continue to use the `joined` variable we created before, where we've chosen the alphabetically first postal code for each practice. Additionally, some postal codes will have multiple `'bnf_name'` with the same number of items prescribed for the maximum. In this case, we'll take the alphabetically first `'bnf_name'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# practices[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'bnf_code': '0101010G0AAABAB',\n",
       " 'items': 2,\n",
       " 'practice': 'N81013',\n",
       " 'bnf_name': 'Co-Magaldrox_Susp 195mg/220mg/5ml S/F',\n",
       " 'nic': 5.98,\n",
       " 'act_cost': 5.56,\n",
       " 'quantity': 1000,\n",
       " 'postal_code': 'SK11 6JL'}"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined[0] #scripts joined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# total_of_item = sum([i['items'] for i in joined])\n",
    "# total_of_item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we need to calculate the total items of each `'bnf_name'` prescribed in each `'post_code'`. Use the techniques we developed in the previous questions to calculate these totals. You should have 141196 `('post_code', 'bnf_name')` groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "big_group = group_by_field2(joined, 'postal_code', 'bnf_name').items()\n",
    "# list(big_group)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_items_by_bnf_post= {name: sum(group['items'] for group in groups) for name, groups in big_group}\n",
    "total_items_by_bnf_post[('S65 1DA', 'Ticagrelor_Tab 90mg')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(total_items_by_bnf_post) == 141196"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's use `total_items` to find the maximum item total for each postal code. To do this, we will want to regroup `total_items_by_bnf_post` by `'post_code'` only, not by `('post_code', 'bnf_name')`. First let's turn `total_items` into a list of dictionaries (similar to `scripts` or `practices`) and then group it by `'post_code'`. You should have 118 groups in the resulting `total_items_by_post` after grouping `total_items` by `'post_code'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_items= [{'post_code': tup[0], 'bnf_name': tup[1], 'total' : tot} for tup, tot in list(total_items_by_bnf_post.items())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_items_by_post = group_by_field2(total_items, ('post_code'))\n",
    "assert len(total_items_by_post) == 118"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "# len(total_items_by_post.values())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will aggregate the groups in `total_by_item_post` to create `max_item_by_post`. Some `'bnf_name'` have the same item total within a given postal code. Therefore, if more than one `'bnf_name'` has the maximum item total in a given postal code, we'll take the alphabetically first `'bnf_name'`. We can do this by [sorting](https://docs.python.org/2.7/howto/sorting.html) each group according to the item total and `'bnf_name'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_items_by_post = [sorted(group, key=lambda grip: (grip['total'], grip['bnf_name']), reverse=True)[0] \n",
    "                     for group in total_items_by_post.values()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to express the item totals as a proportion of the total amount of items prescribed across all `'bnf_name'` in a postal code, we'll need to use the total items prescribed that we previously calculated as `items_by_post`. Calculate the proportions for the most common `'bnf_names'` for each postal code. Format your answer as a list of tuples: `[(post_code, bnf_name, total)]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "result= [(item['post_code'], item['bnf_name'],item['total'] /dict(items_by_post)[item['post_code']]) \n",
    "         for item in max_items_by_post]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Take the first 100 elements and pass it to the grader\n",
    "items_by_region = sorted(result)[:100]\n",
    "items_by_region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================\n",
      "Your score:  1.0\n",
      "==================\n"
     ]
    }
   ],
   "source": [
    "grader.score.pw__items_by_region(items_by_region)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Copyright &copy; 2019 The Data Incubator.  All rights reserved.*"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "nbclean": true
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
